Create Staging Tables in Staging Database and Populate the Staging Tables 7

Download the Sql File attached to the

Run Script that is attached to create your Staging tables in the Staging Database. Once ran the tables will be made up of these columns.

Using SSIS Toolbox, the following components will be drag in the Control Flow Dashboard;

Drag Execute SQL Task. - Double Click and Add Connection manager and Write Sql Query
Right Click on Control Flow tab - select Variables fron the Menu and Add Variables JsonFileName and JsonPathName and provide the name and path respectively
Drag and drop the Script Task - Double Click and Add the Varibles and Click on the Edit button and Add the C# code provided

#region Namespaces
using System;
using System.Data;
using
Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
#endregion
 
string strFileName;     
string strFilePath;       
 
strFilePath = Dts.Variables["User::JSONFILEPATH"].Value.ToString();
strFileName = Path.GetFileName(strFilePath);
 
 Dts.Variables["User::JSONFILENAME"].Value = strFileName;

Drag and drop the Data Flow Task - Double Click
Drag and Drop the Script Component and select Source - Double Click on the Script tab and Add the JsonFilePath variable
Go to the Input and Output Column tab - Add the output columns and enter the column names that macthes to the SQL table and make sure that if there is an int column in the sql table schema provide that same int datatype in the output column and for all other datatype select string with 255 length.
Go to the script tab - Click on the Edit Button and Right Click on the Reference and add Web Extensions. Add a class to the Main Scipt and Add the code provided for Class and Main Script.

C# Code for Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NYCHRNameSpace

{
    class NYCPayroll
    {
        public string FiscalYear { get; set; }
        public string PayrollNumber { get; set; }
        public string AgencyName { get; set; }
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string MidInit { get; set; }
        public string AgencyStartDate { get; set; }
        public string WorkLocationBorough { get; set; }
        public string TitleDescription { get; set; }
        public string LeaveStatusasofJune30 { get; set; }
        public string BaseSalary { get; set; }
        public string PayBasis { get; set; }
        public string RegularHours { get; set; }
        public string RegularGrossPaid { get; set; }
        public string OTHours { get; set; }
        public string TotalOTPaid { get; set; }
        public string TotalOtherPay { get; set; }

    }
}

?C# Code for Main

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Collections.Generic;
using System.Text;
using System.Web.Script.Serialization;
using System.IO;
using NYCHRNameSpace;
#endregion
string readfile = Variables.JSONFILEPATH; 
  String jsonFileContent =           File.ReadAllText(readfile); 
        JavaScriptSerializer js = new
JavaScriptSerializer() { MaxJsonLength = 86753090
};
      List<NYCPayroll> NYCPayrollData = js.Deserialize<List<NYCPayroll>>(jsonFileContent);
       foreach (NYCPayroll DataList in NYCPayrollData)

        { Output0Buffer.AddRow();

            Output0Buffer.FiscalYear = DataList.FiscalYear;
            Output0Buffer.PayrollNumber = DataList.PayrollNumber;
            Output0Buffer.AgencyName = DataList.AgencyName;
            Output0Buffer.LastName = DataList.LastName;
            Output0Buffer.FirstName = DataList.FirstName;
            Output0Buffer.MidInit = DataList.MidInit;
            Output0Buffer.AgencyName = DataList.AgencyName;
            Output0Buffer.AgencyStartDate = DataList.AgencyStartDate;
            Output0Buffer.WorkLocationBorough = DataList.WorkLocationBorough;
            Output0Buffer.TitleDescription = DataList.AgencyName;
            Output0Buffer.LeaveStatusasofJune30 = DataList.LeaveStatusasofJune30;
            Output0Buffer.BaseSalary = DataList.BaseSalary;
            Output0Buffer.PayBasis = DataList.PayBasis;
            Output0Buffer.RegularHours = DataList.RegularHours;
            Output0Buffer.RegularGrossPaid = DataList.RegularGrossPaid;
            Output0Buffer.OTHours = DataList.OTHours;
            Output0Buffer.TotalOTPaid = DataList.TotalOTPaid;
            Output0Buffer.TotalOtherPay = DataList.TotalOtherPay;

        }

Drag and Drop the Derived Column transformation - add the new column Filename and Insert date

· Drag and drop the Data Conversion transformation - to convert the appropriate Data Types

· Drag and drop the OLEDB Destination - add the destination connection manager and select the table to load the data

DDerived Column Code:

Data Conversion:

Using the Script Component in SSIS

https://mindmajix.com/ssis/using-script-component